import pymysql
from datetime import datetime
def createSqlTable(db,cursor,sqlTablename):
    sql="CREATE TABLE IF NOT EXISTS {} (id int auto_increment primary key, create_time  timestamp, log_level   varchar(5), log_count   int,log_detail   text);".format(sqlTablename)
    cursor.execute(sql)
    db.commit()
def connectMysql():
    db = pymysql.connect(
        user="root",
        password="root",
        host="localhost",
        database="applog"
    )
    cursor = db.cursor()
    return db, cursor
def closeConnectMysql(db,cursor):
    db.close()
    cursor.close()
def getStream():
    f=open("D:/a.log", "r", encoding="utf-8")
    return f
def readTxt(f):
    content=f.read()
    return content.split("\n")
def remove_empty_strings(lst):
    return [s for s in lst if s != '']

def getlogsLevelAndWirteMysql(db,cursor,sqlTable,logs):
    for log in logs:
        log=log.replace("   "," ")
        log = log.split(" ",maxsplit=7)
        if len(log)<=5:
            continue
        log=remove_empty_strings(log)
        log[5]=log[5].replace(" ","").replace(":","").replace("-","").replace(".","").replace("'","").replace(",","")
        writeLogMysql(db,cursor,sqlTable,log)
def selectTableByLogdetail(db,cursor,sqlTable,logdetail):
    sql = "select count(*) from {} where log_detail='{}'".format(sqlTable, logdetail)
    cursor.execute(sql)
    result= cursor.fetchone()
    db.commit()
    return result[0]

def getDateTime():
    date = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    return date

def insertLogToMysql(db,cursor,sqlTable,log):
    if log[4]=="F" or log[4]=="E":
        sql = "INSERT INTO {}(create_time,log_level,log_count,log_detail) VALUES (%s,%s,%s,%s)".format(sqlTable)
        values = (getDateTime(),log[4],0,log[5])
    else:
        sql = "INSERT INTO {}(create_time,log_level,log_count) VALUES (%s,%s,%s)".format(sqlTable)
        values = (getDateTime(), log[4], 0)
    cursor.execute(sql, values)
    db.commit()

def updateLogToMysql(db,cursor,sqlTable,log):
    timestr=getDateTime()
    sql = "update {} set log_count=log_count+1,create_time='{}'".format(sqlTable,timestr)
    cursor.execute(sql)
    db.commit()

def writeLogMysql(db,cursor,sqlTable,log):
    if selectTableByLogdetail(db,cursor,sqlTable,log[5])==0 or selectTableByLogdetail(db,cursor,sqlTable,log[5])==-1:
        insertLogToMysql(db,cursor,sqlTable,log)
        return
    updateLogToMysql(db,cursor,sqlTable,log)
